/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Thinh Hung
 */
public class DatabaseConnection {
    // Declare a Connection object
    protected Connection conn;
    protected int itemsPerPage = 5;
    private ConfigUtil configUtil;

    /**
     * Default Constructor
     */
    public DatabaseConnection() {
        configUtil = new ConfigUtil();
        try {
            int i = Integer.parseInt(configUtil.getConfig(ConfigUtil.ITEMS_PER_PAGE));
            itemsPerPage = i > 0 ? i : 5;
        } catch (Exception ex) {
        }
    }

    /**
     * Used to open the connection
     */
    public void openConnection() {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            // Used to set value for conn variable
            String url = "jdbc:sqlserver://" + configUtil.getConfig(ConfigUtil.DB_HOST) +
                    ":" + configUtil.getConfig(ConfigUtil.DB_PORT) +
                    ";databaseName=" + configUtil.getConfig(ConfigUtil.DB_NAME);

            conn = DriverManager.getConnection(url, configUtil.getConfig(ConfigUtil.DB_USERNAME), configUtil.getConfig(ConfigUtil.DB_PASSWORD));
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Used to close the connection
     */
    public void closeConnection() {
        try {
            conn.close();
            conn = null;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Used to get ResultSet of a Sql Statement
     * 
     * @param sql
     * @return ResultSet
     */
    public ResultSet getResultset(String sql) {
        try {
            Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql);
            return rs;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }
}
